﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Person.DS;
using Person.Classes;

namespace Person.DS
{
    public class DS_DB:IPerson
    {
        private string path;
        public string Path { set { path = value; } }
        private IPerson next = null;
        public IPerson Next { set { next = value; } }
        public static OleDbConnection Connection;
        public DS_DB() { }
        public DS_DB(string _path) { Path = _path; }
        public IPerson isReady(string _ext, string _path)
        {
            IPerson ret = null;
            if ("db" == _ext) { ret = this; ret.Path = _path; }
            else if (next != null) { ret = next.isReady(_ext, _path); }
            return ret;
        }
        public void Create(Persona p)
        {
            OleDbConnectionStringBuilder stringBuilder = new OleDbConnectionStringBuilder { ConnectionString = path };
            DS_DB.Connection = new OleDbConnection(stringBuilder.ConnectionString);
            Persona.MakeDBStrFromPersona(p);
        }
        public List<Persona> Read()
        {
            List<Persona> list = new List<Persona>();
            DS_DB.Connection.Open();
            OleDbCommand command = DS_DB.Connection.CreateCommand();
            command.CommandText = "SELECT * FROM Persona";
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Persona per = Persona.GetPersonaFromDB(reader);
                list.Add(per);
            }
            DS_DB.Connection.Close();
            return list;
        }
        public List<Persona> Read(int ID)
        {
            List<Persona> list = new List<Persona>();
            DS_DB.Connection.Open();
            OleDbCommand command = DS_DB.Connection.CreateCommand();
            command.CommandText = "SELECT * FROM Persona WHERE Persona.ID="+ID;
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Persona per = Persona.GetPersonaFromDB(reader);
                list.Add(per);
            }
            DS_DB.Connection.Close();
            return list;
        }
        public void Update(Persona per)
        {
            DS_DB.Connection.Open();
            OleDbCommand command = DS_DB.Connection.CreateCommand();
            command.CommandText = "UPDATE Persona SET Fname=\'" + per.Fname + "\'" + ",Lname=\'" + per.Lname + "\'" + ", Age=" + per.Age + "  WHERE Persona.ID=" + per.ID;
            OleDbDataReader reader = command.ExecuteReader();
            DS_DB.Connection.Close();
        }
        public void Delete(int ID)
        {
            DS_DB.Connection.Open();
            OleDbCommand command = DS_DB.Connection.CreateCommand();
            command.CommandText = "DELETE FROM Persona WHERE Persona.ID=" + ID;
            OleDbDataReader reader = command.ExecuteReader();
            DS_DB.Connection.Close();
        }
    }
}
